"""
Case Type   : 导入导出、备份恢复工具支持M*兼容性
Case Name   : 导出导入alter table相关语法
Create At   : 2023.05.04
Owner       : z@wan005
Description :
    1.创建兼容b库
    2.兼容b库下建表后使用alter table语句添加索引
    3.alter table支持修改row_format
    4.alter table rename 表名
    5.alter table支持禁用和启用非唯一索引
    6.alter table删除索引
    7.alter table删除外键约束和主键约束
    8.alter table:重建表
    9.alter table修改索引名
    10.alter tabletimestamp列增加/删除on update current_timestamp属性
    11.使用gs_dump工具导出纯文本格式
    12.gsql导入
    13.查询导入后表信息
    14.查询导入后表可选项信息
    15.查询rename后的表信息
    16.查询禁用及删除索引后的表
    17.查询重命名索引后的表
    18.查询修改列属性后的表
    19.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
    5.成功
    6.成功
    7.成功
    8.成功
    9.成功
    10.成功
    11.成功
    12.成功
    13.表1导入成功，含索引
    14.表导入成功，row_format选项不显示
    15.rename后的表导入成功
    16.表导入成功，表6索引禁用，表7索引可用，表8~表11的索引删除成功
    17.表导入成功，索引导入成功
    18.表导入成功
    19.成功
History     :
"""

import os
import unittest
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro


class GsDump0049(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.sh_primary = CommonSH('PrimaryDbUser')
        self.dbuser_node = Node('PrimaryDbUser')
        self.constant = Constant()
        self.com = Common()
        self.db_name = "db_dump_0049"
        self.tb_name = "tb_dump_0049"
        self.dump_path = os.path.join(macro.DB_BACKUP_PATH, 'dump0049')
        self.default_value = self.com.show_param('support_extended_features')

    def test_server_tools(self):
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.log.info('---修改参数为on---')
        msg = self.sh_primary.execute_gsguc('set',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            'support_extended_features = on')
        self.log.info(msg)
        self.assertTrue(msg)
        msg = self.sh_primary.restart_db_cluster()
        self.log.info(msg)
        status = self.sh_primary.get_db_cluster_status()
        self.assertTrue("Degraded" in status or "Normal" in status)

        text = '----step1: 创建兼容b库 expect: 成功----'
        self.log.info(text)
        sql_cmd = f"drop database if exists {self.db_name};" \
                  f"create database {self.db_name} dbcompatibility 'b';" \
                  f"drop database if exists {self.db_name}_01;" \
                  f"create database {self.db_name}_01 dbcompatibility 'b';"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd)
        self.log.info(sql_result)
        self.assertEqual(2, sql_result.count(
            self.constant.CREATE_DATABASE_SUCCESS), '执行失败:' + text)

        text = '----step2: 兼容b库下建表后使用alter table语句添加索引 expect: 成功---'
        self.log.info(text)
        sql_cmd = f'''drop table if exists {self.tb_name};
        create table {self.tb_name}(id int)compression=pglz;
        alter table {self.tb_name} add index index_id using btree(id)\
        comment 'id列索引' visible;'''
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_result,
                      '执行失败:' + text)
        self.assertIn(self.constant.ALTER_TABLE_MSG, sql_result,
                      '执行失败:' + text)

        text = '----step3: alter table支持修改row_format expect: 成功---'
        self.log.info(text)
        sql_cmd = f"drop table {self.tb_name}_01,{self.tb_name}_02," \
                  f"{self.tb_name}_03,{self.tb_name}_04;" \
                  f"create table {self.tb_name}_01(c int) " \
                  f"row_format = default;" \
                  f"alter table {self.tb_name}_01 row_format = dynamic;" \
                  f"create table {self.tb_name}_02(c int) " \
                  f"row_format = default;" \
                  f"alter table {self.tb_name}_02 row_format = compressed;" \
                  f"create table {self.tb_name}_03(c int) " \
                  f"row_format = default;" \
                  f"alter table {self.tb_name}_03 row_format = compact;" \
                  f"create table {self.tb_name}_04(c int) " \
                  f"row_format = dynamic;" \
                  f"alter table {self.tb_name}_04 row_format = default;"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertEqual(4,
                         sql_result.count(self.constant.TABLE_CREATE_SUCCESS),
                         '执行失败:' + text)
        self.assertEqual(4,
                         sql_result.count(self.constant.ALTER_TABLE_MSG),
                         '执行失败:' + text)

        text = '----step4: alter table rename 表名 expect: 成功---'
        self.log.info(text)
        sql_cmd = f"drop table if exists {self.tb_name}_05;" \
                  f"create table {self.tb_name}_05(c int);" \
                  f"alter table {self.tb_name}_05 rename as " \
                  f"new_{self.tb_name}_05;"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_result,
                      '执行失败:' + text)
        self.assertIn(self.constant.ALTER_TABLE_MSG, sql_result,
                      '执行失败:' + text)

        text = '----step5: alter table支持禁用和启用非唯一索引 expect: 成功---'
        self.log.info(text)
        sql_cmd = f"drop table if exists {self.tb_name}_06," \
                  f"{self.tb_name}_07;" \
                  f"create table {self.tb_name}_06(c int, " \
                  f"index using btree(c));" \
                  f"alter table {self.tb_name}_06 disable keys;" \
                  f"create table {self.tb_name}_07(c int, " \
                  f"index using hash(c));" \
                  f"alter table {self.tb_name}_07 enable keys;"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertEqual(2,
                         sql_result.count(self.constant.TABLE_CREATE_SUCCESS),
                         '执行失败:' + text)
        self.assertEqual(2,
                         sql_result.count(self.constant.ALTER_TABLE_MSG),
                         '执行失败:' + text)

        text = '----step6: alter table删除索引 expect: 成功---'
        self.log.info(text)
        sql_cmd = f"drop table if exists {self.tb_name}_08,{self.tb_name}_09;" \
                  f"create table {self.tb_name}_08(c int, index " \
                  f"alter_index_001 using btree(c));" \
                  f"alter table {self.tb_name}_08 drop index " \
                  f"alter_index_001;" \
                  f"create table {self.tb_name}_09(c int, index " \
                  f"alter_index_002 using hash(c));" \
                  f"alter table {self.tb_name}_09 drop key alter_index_002;"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertEqual(2,
                         sql_result.count(self.constant.TABLE_CREATE_SUCCESS),
                         '执行失败:' + text)
        self.assertEqual(2,
                         sql_result.count(self.constant.ALTER_TABLE_MSG),
                         '执行失败:' + text)

        text = '----step7: alter table删除外键约束和主键约束 expect: 成功---'
        self.log.info(text)
        sql_cmd = f"drop table if exists {self.tb_name}_10," \
                  f"{self.tb_name}_11;" \
                  f"create table {self.tb_name}_10(c int primary key);" \
                  f"create table {self.tb_name}_11(c int not null, " \
                  f"constraint fk_symbo foreign key(c) references " \
                  f"{self.tb_name}_10(c));" \
                  f"alter table {self.tb_name}_11 drop foreign key fk_symbo;" \
                  f"alter table {self.tb_name}_10 drop primary key;"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertEqual(3,
                         sql_result.count(self.constant.TABLE_CREATE_SUCCESS),
                         '执行失败:' + text)
        self.assertEqual(2,
                         sql_result.count(self.constant.ALTER_TABLE_MSG),
                         '执行失败:' + text)

        text = '----step8: alter table:重建表 expect: 成功---'
        self.log.info(text)
        sql_cmd = f"drop table if exists {self.tb_name}_12," \
                  f"{self.tb_name}_13;" \
                  f"create table {self.tb_name}_12(c int);" \
                  f"alter table {self.tb_name}_12 force;" \
                  f"create table {self.tb_name}_13(c int, index " \
                  f"using hash(c));" \
                  f"alter table {self.tb_name}_13 force;"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_result,
                      '执行失败:' + text)
        self.assertEqual(2, sql_result.count('VACUUM'), '执行失败:' + text)

        text = '----step9: alter table修改索引名 expect: 成功---'
        self.log.info(text)
        sql_cmd = f"drop table if exists {self.tb_name}_14," \
                  f"{self.tb_name}_15,{self.tb_name}_16,{self.tb_name}_17;" \
                  f"create table {self.tb_name}_14(c int, index idx1 " \
                  f"using btree(c));" \
                  f"create table {self.tb_name}_15(c int, index idx2 " \
                  f"using hash(c));" \
                  f"create table {self.tb_name}_16(c tsvector, index idx3 " \
                  f"using gin(c));" \
                  f"create table {self.tb_name}_17(c tsquery, index idx4 " \
                  f"using gist(c));" \
                  f"alter table {self.tb_name}_14 rename key idx1 to " \
                  f"index_{self.tb_name}_14;" \
                  f"alter table {self.tb_name}_15 rename index idx2 to " \
                  f"key_{self.tb_name}_15;" \
                  f"alter table {self.tb_name}_16 rename index idx3 to " \
                  f"index_{self.tb_name}_16;" \
                  f"alter table {self.tb_name}_17 rename key idx4 to " \
                  f"key_{self.tb_name}_17;"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertEqual(4,
                         sql_result.count(self.constant.TABLE_CREATE_SUCCESS),
                         '执行失败:' + text)
        self.assertEqual(4,
                         sql_result.count(self.constant.ALTER_TABLE_MSG),
                         '执行失败:' + text)

        text = '----step10: alter tabletimestamp列增加/删除on update ' \
               'current_timestamp属性 expect: 成功---'
        self.log.info(text)
        sql_cmd = f"drop table if exists {self.tb_name}_18," \
                  f"{self.tb_name}_19;" \
                  f"create table {self.tb_name}_18(c timestamp);" \
                  f"alter table {self.tb_name}_18 modify c timestamp " \
                  f"on update current_timestamp;" \
                  f"create table {self.tb_name}_19(c timestamp on update " \
                  f"current_timestamp);" \
                  f"alter table {self.tb_name}_19 modify c timestamp;"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=self.db_name)
        self.log.info(sql_result)
        self.assertEqual(2,
                         sql_result.count(self.constant.TABLE_CREATE_SUCCESS),
                         '执行失败:' + text)
        self.assertEqual(2,
                         sql_result.count(self.constant.ALTER_TABLE_MSG),
                         '执行失败:' + text)

        text = '----step11: 使用gs_dump工具导出纯文本格式 expect: 成功----'
        self.log.info(text)
        gs_dump_cmd = f"source {macro.DB_ENV_PATH};" \
                      f"gs_dump " \
                      f"-p {self.dbuser_node.db_port}  " \
                      f"{self.db_name} " \
                      f"-f {self.dump_path}"
        self.log.info(gs_dump_cmd)
        dump_msg = self.dbuser_node.sh(gs_dump_cmd).result()
        self.log.info(dump_msg)
        self.assertIn(f"dump database {self.db_name} successfully", dump_msg,
                      '执行失败:' + text)

        text = '----step12: gsql导入 expect: 成功----'
        self.log.info(text)
        gsql_cmd = f"source {macro.DB_ENV_PATH};" \
                   f"gsql " \
                   f"-p {self.dbuser_node.db_port} " \
                   f"{self.db_name}_01 " \
                   f"-f {self.dump_path}"
        self.log.info(gsql_cmd)
        gsql_msg = self.dbuser_node.sh(gsql_cmd).result()
        self.log.info(gsql_msg)
        self.assertEqual(20, gsql_msg.count('CREATE TABLE'))

        text = '----step13: 查询导入后表信息 expect:表1导入成功，含索引----'
        self.log.info(text)
        sql_cmd = f"show create table {self.tb_name};"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=f'{self.db_name}_01')
        self.log.info(sql_result)
        self.assertIn('CREATE INDEX index_id ON tb_dump_0049 USING btree (id)',
                      sql_result)

        text = '----step14: 查询导入后表可选项信息 expect:表导入成功，' \
               'row_format选项不显示--'
        self.log.info(text)
        sql_cmd = f"show create table {self.tb_name}_01;" \
                  f"show create table {self.tb_name}_02;" \
                  f"show create table {self.tb_name}_03;" \
                  f"show create table {self.tb_name}_04;"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=f'{self.db_name}_01')
        self.log.info(sql_result)
        self.assertNotIn('row_format', sql_result, '执行失败:' + text)

        text = '----step15: 查询rename后的表信息 expect:rename后的表导入成功----'
        self.log.info(text)
        sql_cmd = f"show create table new_{self.tb_name}_05;"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=f'{self.db_name}_01')
        self.log.info(sql_result)
        self.assertIn('new_tb_dump_0049_05', sql_result, '执行失败:' + text)

        text = '----step16: 查询禁用及删除索引后的表 expect:表导入成功，表6索引禁用，' \
               '表7索引可用，表8~表11的索引删除成功----'
        self.log.info(text)
        sql_cmd = f"show index from {self.tb_name}_06;" \
                  f"show index from {self.tb_name}_07;" \
                  f"show index from {self.tb_name}_08;" \
                  f"show index from {self.tb_name}_09;" \
                  f"show index from {self.tb_name}_10;" \
                  f"show index from {self.tb_name}_11;"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=f'{self.db_name}_01')
        self.log.info(sql_result)
        self.assertIn('disabled', sql_result, '执行失败:' + text)
        self.assertIn('tb_dump_0049_07_c_idx', sql_result, '执行失败:' + text)
        self.assertEqual(4, sql_result.count('(0 rows)'), '执行失败:' + text)

        text = '----step17: 查询重命名索引后的表 expect:表导入成功，索引导入成功----'
        self.log.info(text)
        sql_cmd = f"show index from {self.tb_name}_12;" \
                  f"show index from {self.tb_name}_13;" \
                  f"show index from {self.tb_name}_14;" \
                  f"show index from {self.tb_name}_15;" \
                  f"show index from {self.tb_name}_16;" \
                  f"show index from {self.tb_name}_17;"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=f'{self.db_name}_01')
        self.log.info(sql_result)
        msg_lis = ['(0 rows)', 'tb_dump_0049_13_c_idx',
                   'index_tb_dump_0049_14', 'key_tb_dump_0049_15',
                   'index_tb_dump_0049_16', 'key_tb_dump_0049_17']
        for msg in msg_lis:
            self.assertIn(msg, sql_result, '查询失败')

        text = '----step18: 查询修改列属性后的表 expect:表导入成功----'
        self.log.info(text)
        sql_cmd = f"show create table {self.tb_name}_18;" \
                  f"show create table {self.tb_name}_19;"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname=f'{self.db_name}_01')
        self.log.info(sql_result)
        self.assertIn(
            'c timestamp(0) with time zone ON UPDATE CURRENT_TIMESTAMP(0)',
            sql_result)
        self.assertIn('c timestamp(0) with time zone', sql_result)

    def tearDown(self):
        text = '---step19: 清理环境 expect: 成功----'
        self.log.info(text)
        sql_cmd = f"drop database if exists {self.db_name};" \
                  f"drop database if exists {self.db_name}_01;"
        self.log.info(sql_cmd)
        sql_result = self.sh_primary.execut_db_sql(sql_cmd,
                                                   dbname='postgres')
        self.log.info(sql_result)
        rm_cmd = f"rm -rf {self.dump_path}"
        self.log.info(rm_cmd)
        rm_msg = self.dbuser_node.sh(rm_cmd).result()
        self.log.info(rm_msg)
        msg = self.sh_primary.execute_gsguc('set',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            f'support_extended_features '
                                            f'= {self.default_value}')
        self.log.info(msg)
        self.assertTrue(msg)
        msg = self.sh_primary.restart_db_cluster()
        self.log.info(msg)
        status = self.sh_primary.get_db_cluster_status()
        self.assertTrue("Degraded" in status or "Normal" in status)
        self.assertEqual(2,
                         sql_result.count(self.constant.DROP_DATABASE_SUCCESS),
                         '执行失败:' + text)
        self.assertEqual('', rm_msg)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
